import pymysql


# import dbutils.pooled_db
# conn = pymysql.connect(host="localhost",
#                        port=3306,
#                        user="root",
#                        password="tian1985",
#                        database="mystock")
# cursor = conn.cursor()
#
# sql = "select * from base_stock"
# cursor.execute(sql)
# result = cursor.fetchall()
# for row in result:
#     print(row)
#
# cursor.close()
# conn.close()

class MysqlHelper:
    def __init__(self):
        self.host = "localhost"
        self.port = 3306
        self.user = "root"
        self.password = "tian1985"
        self.db = "mystock"
        self.charset = "utf8"
        self.con = None
        self.cursor = None

    def create_con(self):
        """
        创建连接
        """
        try:
            self.con = pymysql.connect(host=self.host, port=self.port, user=self.user, password=self.password,
                                       database=self.db, charset='utf8')
            self.cursor = self.con.cursor()
            return True
        except Exception as e:
            print(e)
            return False

    def close_con(self):
        """
        关闭链接
        """
        if self.cursor:
            self.cursor.close()
        if self.con:
            self.con.close()

    # sql执行
    def execute_sql(self, sql):
        """
        执行插入/更新/删除语句
        """
        try:
            self.create_con()
            # print(sql)
            self.cursor.execute(sql)
            self.con.commit()
        except Exception as e:
            print(e)
        finally:
            self.close_con()

    def execute_sqlArgs(self, sql, *args):
        """
        执行插入/更新/删除语句
        """
        try:
            self.create_con()
            # print(sql)
            self.cursor.execute(sql, args)
            self.con.commit()
        except Exception as e:
            print(e)
        finally:
            self.close_con()

    def execute_sql_list(self, sql, arg_list):
        """
        执行插入/更新/删除语句
        """
        try:
            self.create_con()
            # print(sql)
            for data in arg_list:
                self.cursor.execute(sql, data)
            self.con.commit()
        except Exception as e:
            print(e)
        finally:
            self.close_con()

    def execute_many(self,sql,args):
        """
        批量提交
        :param sql: 要提交的sql
        :param args: list参数
        :return:
        """
        try:
            self.create_con()
            self.cursor.executemany(sql,args)
            self.con.commit()
        except Exception as e:
            print(e)
        finally:
            self.close_con()
    def select(self, sql, *args):
        """
        执行查询语句
        """
        try:
            self.create_con()
            # print(sql)
            self.cursor.execute(sql, args)
            res = self.cursor.fetchall()  #返回tuple
            # data_list=[row[0] for row in res] 返回list
            return res
        except Exception as e:
            print(e)
            return False
        finally:
            self.close_con()
